<html>
<head>
<title>MySQL</title>
</head>

<body bgcolor="#ffffff">

<h1>MySQL</h1>

This driver implements read and write access for spatial data in 
<a href="http://www.mysql.org/">MySQL</a> tables.  This functionality 
was introduced in GDAL/OGR 1.3.2.  <p>

When opening a database, it's name should be specified in the form
"MYSQL:dbname[,options]" where the options can include comma seperated
items like "user=*userid*", "password=*password*", "host=*host*" and 
"port=*port*".<p>

As well, a "tables=*table*;*table*..." option can be
added to restrict access to a specific list of tables in the database.  This
option is primarily useful when a database has a lot of tables, and scanning
all their schemas would take a significant amount of time. <p>

Currently all regular user tables are assumed to be layers from an OGR
point of view, with the table names as the layer names.  Named views are
not currently supported.<p>

If a single integer field is a primary key, it will be used as the FID 
otherwise the FID will be assigned sequentially, and fetches by FID will
be extremely slow. <p>

<p>By default, SQL statements are passed directly to the MySQL database engine.
It's also possible to request the driver to handle SQL commands 
with <a href="/ogr/ogr_sql.html">OGR SQL</a> engine, 
by passing <strong>"OGRSQL"</strong> string to the ExecuteSQL() 
method, as name of the SQL dialect.</p>
 
<h2>Caveats</h2>
<ul>
    <li>
        In the case of a layer defined by a SQL statement, fields either named
        "OGC_FID" or those that are defined as NOT NULL, are a PRIMARY KEY, and 
        are an integer-like field will be assumed to be the FID.
    </li>
    <li>
        Geometry fields are read from MySQL using WKB format.  Versions older 
        than 5.0.16 of MySQL are known to have issues with some WKB 
        generation and may not work properly.
    </li>
    <li>
        The OGR_FID column, which can be overridden with the MYSQL_FID layer 
        creation option, is implemented as a 
        <b>INT UNIQUE NOT NULL AUTO_INCREMENT</b> field.  This 
        appears to implicitly create an index on the field.
    </li>
    <li>
        The geometry column, which defaults to <i>SHAPE</i> and can be overridden 
        with the GEOMETRY_NAME layer creation option, is created as a 
        <b>NOT NULL</b> column in unless SPATIAL_INDEX is disabled.  By default
        a spatial index is created at the point the table is created.
    </li>
    <li>
        SRS information is stored using the OGC Simple Features for SQL layout, with 
        <i>geometry_columns</i> and <i>spatial_ref_sys</i> metadata tables being 
        created in the specified database if they do not already exist.  The 
        <i>spatial_ref_sys</i> table is <b>not</b> pre-populated with SRS and 
        EPSG values like PostGIS.  If no EPSG code is found for a given table, 
        the MAX(SRID) value will be used.
    </li>

	<li>
	    Connection timeouts to the server can be specified with the <b>MYSQL_TIMEOUT</b>
	    environment variable.  For example, SET MYSQL_TIMEOUT=3600.  It is possible this 
	    variable only has an impact when the OS of the MySQL server is Windows.
	</li>
	<li>
	    The MySQL driver opens a connection to the database using CLIENT_INTERACTIVE mode.
	    You can adjust this setting (interactive_timeout) in your mysql.ini or mysql.cnf 
	    file of your server to your liking.
	</li>
	<li>
	    We are using WKT to insert geometries into the database.  
	    If you are inserting big geometries, you will need to be aware of the <i>max_allowed_packet</i> 
	    parameter in the MySQL configuration.  By default it is set to 1M, but this will not 
	    be large enough for really big geometries.  If you get an error message like: 
	    <i>Got a packet bigger than 'max_allowed_packet' bytes</i>, you will need to increase 
	    this parameter.
	</li>
	    
</ul>

<h2>Creation Issues</h2>

The MySQL driver does not support creation of new datasets (a database
within MySQL), but it does allow creation of new layers within an
existing database.<P>
    
By default, the MySQL driver will attempt to preserve the precision 
of OGR features when creating and reading MySQL layers.  For integer fields 
with a specified width, it will use <b>DECIMAL</b> as the MySQL field 
type with a specified precision of 0.  For real fields, it will use 
<b>DOUBLE</b> with the specified width and precision. For string fields 
with a specified width, <b>VARCHAR</b> will be used.<p>

The MySQL driver makes no allowances for character encodings at this time.<p>

The MySQL driver is not transactional at this time.<p>




    
<h3>Layer Creation Options</h3>

<ul>
    <li>
        <b>OVERWRITE</b>: This may be "YES" to force an existing layer of the 
        desired name to be destroyed before creating the requested layer.
    </li>
    <li>
        <b>LAUNDER</b>: This may be "YES" to force new fields created on this 
        layer to have their field names "laundered" into a form more 
        compatible with MySQL.  This converts to lower case and converts 
        some special characters like "-" and "#" to "_".  If "NO" exact names 
        are preserved.  The default value is "YES".
    </li>
    <li>
        <b>PRECISION</b>: This may be "TRUE" to attempt to preserve field 
        widths and precisions for the creation and reading of MySQL layers.
        The default value is "TRUE".
    </li>
    <li>
        <b>MYSQL_GEOM_COLUMN</b>: This option specifies the name of the 
        geometry column.  The default value is "SHAPE".
    </li>
    <li>
        <b>MYSQL_FID</b>: This option specifies the name of the FID column.
        The default value is "OGR_FID"
    </li>
    <li>
        <b>SPATIAL_INDEX</b>: May be "NO" to stop automatic creation of 
        a spatial index on the geometry column, allowing NULL geometries 
        and possibly faster loading. 
    </li>
    <li>
        <b>ENGINE</b>: Optionally specify database engine to use.  In MySQL 
        4.x this must be set to MyISAM for spatial tables.
    </li>
</ul>

The following example datasource name opens the database schema 
<i>westholland</i> with password <i>psv9570</i> for userid <i>root</i>
on the port <i>3306</i>.  No hostname is provided, so localhost is assumed.
The tables= directive means that only the bedrijven table is scanned and
presented as a layer for use.<p>

<pre>
MYSQL:westholland,user=root,password=psv9570,port=3306,tables=bedrijven
</pre>

The following example uses ogr2ogr to create copy the world_borders layer 
from a shapefile into a MySQL table.  It overwrites a table with the existing 
name <i>borders2</i>, sets a layer creation option to specify the geometry 
column name to <i>SHAPE2</i>.

<pre>
ogr2ogr -f MySQL MySQL:test,user=root world_borders.shp -nln borders2 -update -overwrite -lco GEOMETRY_NAME=SHAPE2 
</pre>

The following example uses ogrinfo to return some summary information about the borders2 
layer in the test database.
<pre>
ogrinfo MySQL:test,user=root borders2 -so

    Layer name: borders2
    Geometry: Polygon
    Feature Count: 3784
    Extent: (-180.000000, -90.000000) - (180.000000, 83.623596)
    Layer SRS WKT:
    GEOGCS["GCS_WGS_1984",
        DATUM["WGS_1984",
            SPHEROID["WGS_84",6378137,298.257223563]],
        PRIMEM["Greenwich",0],
        UNIT["Degree",0.017453292519943295]]
    FID Column = OGR_FID
    Geometry Column = SHAPE2
    cat: Real (0.0)
    fips_cntry: String (80.0)
    cntry_name: String (80.0)
    area: Real (15.2)
    pop_cntry: Real (15.2)
</body>
</html>
